Allows you to specify advanced options for your Solver model. This function and its arguments correspond to the options in the Solver Options dialog box.
Before you use this function, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library subfolder.
SolverOptions(MaxTime, Iterations, Precision, AssumeLinear, StepThru, Estimates, Derivatives, Search, IntTolerance, Scaling, Convergence, AssumeNonNeg)
MaxTime Optional Variant. The maximum amount of time (in seconds) Microsoft Excel will spend solving the problem. The value must be a positive integer. The default value 100 is adequate for most small problems, but you can enter a value as high as 32,767.
Iterations Optional Variant. The maximum number of iterations Microsoft Excel will use in solving the problem. The value must be a positive integer. The default value 100 is adequate for most small problems, but you can enter a value as high as 32,767.
Precision Optional Variant. A number between 0 (zero) and 1 that specifies the degree of precision to be used in solving the problem. The default precision is 0.000001. A smaller number of decimal places (for example, 0.0001) indicates a lower degree of precision. In general, the higher the degree of precision you specify (the smaller the number), the more time Solver will take to reach solutions.
AssumeLinear Optional Variant. True to have Solver assume that the underlying model is linear. This speeds the solution process, but it should be used only if all the relationships in the model are linear. The default value is False.
StepThru Optional Variant. True to have Solver pause at each trial solution. You can pass Solver a macro to run at each pause by using the ShowRef argument of the SolverSolve function. False to not have Solver pause at each trial solution. The default value if False.
Estimates Optional Variant. Specifies the approach used to obtain initial estimates of the basic variables in each one-dimensional search: 1 represents tangent estimates, and 2 represents quadratic estimates. Tangent estimates use linear extrapolation from a tangent vector. Quadratic estimates use quadratic extrapolation; this may improve the results for highly nonlinear problems. The default value is 1 (tangent estimates).
Derivatives Optional Variant. Specifies forward differencing or central differencing for estimates of partial derivatives of the objective and constraint functions: 1 represents forward differencing, and 2 represents central differencing. Central differencing requires more worksheet recalculations, but it may help with problems that generate a message saying that Solver couldn’t improve the solution. With constraints whose values change rapidly near their limits, you should use central differencing. The default value is 1 (forward differencing).
Search Optional Variant. Use the Search options to specify the search algorithm that will be used at each iteration to decide which direction to search in: 1 represents the Newton search method, and 2 represents the conjugate search method. Newton, which uses a quasi-Newton method, is the default search method. This method typically requires more memory than the conjugate search method, but it requires fewer iterations. Conjugate gradient searching requires less memory than the Newton search method, but it typically requires more iterations to reach a particular level of accuracy. You can try this method if you have a large problem and memory usage is a concern. Conjugate searching is especially useful if stepping through the iterations reveals slow progress between successive trial points.
IntTolerance Optional Variant. A decimal number between 0 (zero) and 1 that specifies the degree of integer tolerance. This argument applies only if integer constraints have been defined. You can adjust the tolerance figure, which represents the percentage of error allowed in the optimal solution when an integer constraint is used on any element of the problem. A higher degree of tolerance (allowable percentage of error) would tend to speed up the solution process.
Scaling Optional Variant. If two or more constraints differ by several orders of magnitude, True to have Solver scale the constraints to similar orders of magnitude during computation. This is useful when the inputs (in the By Changing Cells box in the Solver Parameters dialog box) and outputs (in the Set Target Cell and Subject to the Constraints boxes in the Solver Parameters dialog box) have large differences in magnitude
Convergence Optional Variant. A number between 0 (zero) and 1 that specifies the degree of convergence tolerance for the nonlinear Solver. When the relative change in the target cell value is less than this tolerance for the last five iterations, Solver stops and displays the message "Solver converged to the current solution. All constraints are satisfied."
AssumeNonNeg Optional Variant. True to have Solver assume a lower limit of 0 (zero) for all adjustable (changing) cells that don’t have explicit lower limits in the Constraint list box (the cells must contain nonnegative values). False to have Solver use only the limits specified in the Constraint list box.
This example sets the Precision option to .001.
Worksheets("Sheet1").Activate
SolverReset
SolverOptions Precision:=0.001
SolverOK SetCell:=Range("TotalProfit"), _
MaxMinVal:=1, _
ByChange:=Range("C4:E6")
SolverAdd CellRef:=Range("F4:F6"), _
Relation:=1, _
FormulaText:=100
SolverAdd CellRef:=Range("C4:E6"), _
Relation:=3, _
FormulaText:=0
SolverAdd CellRef:=Range("C4:E6"), _
Relation:=4
SolverSolve UserFinish:=False
SolverSave SaveArea:=Range("A33")